package com.opennetexam.manager.mapper;

import com.opennetexam.manager.databasemodel.*;
import com.opennetexam.manager.dto.*;
import com.opennetexam.manager.sqlprovider.ExamineeArrangeProvider;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

/**
 * @author 奶茶
 * Created on 2017/11/13.
 */
@Mapper
public interface ExamineeArrangeMapper {

    //@SelectProvider(type = ExamineeArrangeProvider.class,method = "findExamCountList")
    //List<ExamCount> findExamCountList();

    @SelectProvider(type = ExamineeArrangeProvider.class,method = "findExamPlanViewModelBySomeWhere")
    List<ExamPlanViewModel> findExamPlanViewModelBySomeWhere(@Param(value = "appId") String appId, @Param(value = "siteId") String siteId, @Param(value = "planType") Short planType,@Param(value = "examPlanName") String examPlanName);

    @SelectProvider(type = ExamineeArrangeProvider.class,method = "findArranges")
    List<ArrangesInfo> findArranges(@Param(value = "planId") String planId, @Param(value = "appId") String appId);

    @Select("SELECT * FROM ExamineeArrange WHERE AppID = #{appId} AND ExamPlanInfoID = #{planId}")
    List<ArrangesInfo> ExamineeArrangesWhere(@Param(value = "planId") String planId, @Param(value = "appId") String appId);

    @Select("SELECT * FROM ExamineeArrange WHERE AppID = #{appId} AND ExamPlanInfoID = #{planId} AND ExamineeID = #{examineeId} AND ExamSubjectID = #{subjectId} LIMIT 1")
    ExamineeArrange getExamineeArrange(@Param(value = "planId") String planId, @Param(value = "appId") String appId,@Param(value = "examineeId") String examineeId,@Param(value = "subjectId") String subjectId);

    @Select("SELECT * FROM ExamPlanInfo WHERE AppID = #{appId} AND ExamPlanInfoID = #{planId} AND SeatByNumberRequired = 0")
    List<ExamPlanInfo> findExamPlanInfoBySomeWhere(@Param(value = "planId") String planId, @Param(value = "appId") String appId);

    @SelectProvider(type = ExamineeArrangeProvider.class,method = "findExamComputerBySomeWhere")
    List<ExamComputer> findExamComputerBySomeWhere(@Param(value = "appId") String appId,@Param(value = "examPlanInfoId") String examPlanInfoId);

    @SelectProvider(type = ExamineeArrangeProvider.class,method = "examineeArrangeWhere")
    List<ExamineeArrange> examineeArrangeWhere(@Param(value = "appId") String appId,@Param(value = "examPlanInfoID") String examPlanInfoID,@Param(value = "examSubjectID") String examSubjectID);

    @SelectProvider(type = ExamineeArrangeProvider.class,method = "examineeNotArrangeWhere")
    List<ExamineeArrange> examineeNotArrangeWhere(@Param(value = "appId") String appId,@Param(value = "examPlanInfoID") String examPlanInfoID,@Param(value = "examSubjectID") String examSubjectID);

    @SelectProvider(type = ExamineeArrangeProvider.class,method = "examineeArrangeMoniter")
    List<ExamineeArrange> examineeArrangeMoniter(@Param(value = "examPlanInfoID") String examPlanInfoID,@Param(value = "examRoomID") String examRoomID,@Param(value = "appID") String appID,@Param(value = "examSceneID") String examSceneID,@Param(value = "examSubjectID") String examSubjectID,@Param(value = "examineeID") String examineeID);

    @Select("SELECT ExamineeID,ExamineeArrangeID FROM ExamineeArrange")
    List<Map<String,Object>> findAllExamineeArrange();

    @Select("SELECT * FROM ExamineeArrange WHERE ExamineeArrangeID = #{examineeArrangeID} LIMIT 1")
    ExamineeArrange getExamineeArrangeById(@Param(value = "examineeArrangeID") String examineeArrangeID);

    @Select("SELECT DISTINCT * FROM ExamineeArrange WHERE AppID = #{appID} AND ExamPlanInfoID = #{examPlanInfoID} AND ExamineeID = #{examineeID}")
    List<ExamineeArrange> findExamineeArrangeBySomeWhereList(@Param("appID")String appID,@Param("examPlanInfoID") Integer examPlanInfoID,@Param("examineeID") Integer examineeID);

    @Select("SELECT DISTINCT * FROM ExamineeArrange WHERE AppID = #{appID} AND ExamPlanInfoID = #{examPlanInfoID} AND ExamRoomID = #{examRoomID} AND ExamSceneID = #{examSceneID} AND ExamineeID = #{examineeID} LIMIT 1")
    ExamineeArrange findExamineeArrangeBySomeWhereInfo(@Param("appID")String appID,@Param("examPlanInfoID") String examPlanInfoID,@Param("examSceneID") String examSceneID,@Param("examRoomID") String examRoomID,@Param("examineeID") String examineeID);

    @Select("SELECT \n" +
            "ea.* \n" +
            "from ExamineeArrange ea \n" +
            "LEFT JOIN ExamSubject es on ea.ExamSubjectID = es.ExamSubjectID \n" +
            "LEFT JOIN Examinee e on ea.ExamineeID = e.ExamineeID \n" +
            "WHERE 1=1  \n" +
            "AND ea.AppID =#{appID} \n" +
            "AND ea.ExamPlanInfoID = #{planId} \n" +
            "AND es.AppID =#{appID} \n" +
            "AND es.ExamPlanInfoID = #{planId} \n" +
            "AND e.ExamSiteID = #{examSiteID} \n" +
            "AND e.CertificateNo =#{certificateNo} \n" +
            "AND e.ExamineeName =#{examineeName}")
    List<ExamineeArrange> findAutoManualSignInRetEAList(@Param("appID")String appID,@Param("examSiteID") String examSiteID,@Param("planId") String planId,@Param("examineeName") String examineeName,@Param("certificateNo") String certificateNo);

    @Select("SELECT \n" +
            "es.* \n" +
            "from ExamineeArrange ea \n" +
            "LEFT JOIN ExamSubject es on ea.ExamSubjectID = es.ExamSubjectID \n" +
            "LEFT JOIN Examinee e on ea.ExamineeID = e.ExamineeID \n" +
            "WHERE 1=1  \n" +
            "AND ea.AppID =#{appID} \n" +
            "AND ea.ExamPlanInfoID = #{planId} \n" +
            "AND es.AppID =#{appID} \n" +
            "AND es.ExamPlanInfoID = #{planId} \n" +
            "AND e.ExamSiteID = #{examSiteID} \n" +
            "AND e.CertificateNo =#{certificateNo} \n" +
            "AND e.ExamineeName =#{examineeName}")
    List<ExamSubject> findAutoManualSignInRetESList(@Param("appID")String appID,@Param("examSiteID") String examSiteID,@Param("planId") String planId,@Param("examineeName") String examineeName,@Param("certificateNo") String certificateNo);

    @Select("SELECT \n" +
            "ea.* \n" +
            "from ExamineeArrange ea \n" +
            "LEFT JOIN Examinee e on ea.ExamineeID = e.ExamineeID \n" +
            "WHERE 1=1  \n" +
            "AND ea.AppID =#{appID} \n" +
            "AND ea.ExamPlanInfoID = #{planId} \n" +
            "AND e.ExamPlanInfoID = #{planId} \n" +
            "AND e.ExamSiteID = #{examSiteID} \n" +
            "AND e.CertificateNo =#{certificateNo} \n" +
            "AND e.ExamineeName =#{examineeName}")
    List<ExamineeArrange> findAutoExamineeArrangeBySomeWhere(@Param("appID")String appID,@Param("examSiteID") String examSiteID,@Param("planId") String planId,@Param("examineeName") String examineeName,@Param("certificateNo") String certificateNo);

    @Insert("INSERT INTO ExamineeArrange (\n" +
            "\tAppID,ExamPlanInfoID,ExamSubjectID,ExamSceneID,ExamRoomID,ExamineeID,ExamineeName,SeatNo,ExamDate,PlanBeginTime,PlanEndTime,Score\n" +
            ") SELECT #{appID},#{examPlanInfoID},#{examSubjectID},#{examSceneID},#{examRoomID},#{examineeID},#{examineeName},#{seatNo},#{examDate},#{planBeginTime},#{planEndTime},#{score}\n" +
            "FROM\n" +
            "\tDUAL\n" +
            "WHERE\n" +
            "\tNOT EXISTS (\n" +
            "\t\tSELECT\n" +
            "\t\t\t1\n" +
            "\t\tFROM\n" +
            "\t\t\tExamineeArrange\n" +
            "\t\tWHERE\n" +
            "\t\t\tExamPlanInfoID = #{examPlanInfoID}\n" +
            "\t\tAND ExamineeID = #{examineeID}\n" +
            "\t\tAND ExamSubjectID = #{examSubjectID}\n" +
            "\t\tAND ExamSceneID = #{examSceneID}\n" +
            "\t\tAND ExamRoomID = #{examRoomID}\n" +
            "\t\tLIMIT 1\n" +
            ")")
    Integer insertExamineeArrange(ExamineeArrange examineeArrange);

    @Insert("INSERT INTO ExamineeArrange (\n" +
            "\tExamineeArrangeID,\n" +
            "\tAppID,\n" +
            "\tExamPlanInfoID,\n" +
            "\tExamSubjectID,\n" +
            "\tExamSceneID,\n" +
            "\tExamRoomID,\n" +
            "\tExamineeID,\n" +
            "\tExamineeName,\n" +
            "\tSeatNo,\n" +
            "\tExamDate,\n" +
            "\tPlanBeginTime,\n" +
            "\tPlanEndTime,\n" +
            "\tScore\n" +
            ")\n" +
            "VALUE\n" +
            "\t(#{examineeArrangeID},#{appID},#{examPlanInfoID},#{examSubjectID},#{examSceneID},#{examRoomID},#{examineeID},#{examineeName},#{seatNo},#{examDate},#{planBeginTime},#{planEndTime},#{score})")
    Integer insertExaminerArrange(ExamineeArrange examineeArrange);

    @Update("UPDATE ExamineeArrange SET AppID = #{appID},ExamPlanInfoID = #{examPlanInfoID},ExamSubjectID = #{examSubjectID},ExamSceneID = #{examSceneID},ExamRoomID = #{examRoomID},ExamineeID = #{examineeID},ExamineeName = #{examineeName},SeatNo = #{seatNo},ExamDate = #{examDate},PlanBeginTime = #{planBeginTime},PlanEndTime = #{planEndTime},Score = #{score} WHERE ExamineeArrangeID = #{examineeArrangeID}")
    Integer updateExamineeArrange(ExamineeArrange examineeArrange);

    @Select("SELECT * FROM ExamineeArrange WHERE AppID = #{appID} AND ExamPlanInfoID = #{examPlanInfoID}")
    List<ExamineeArrange> findExamineeArrangesWhere(@Param("examPlanInfoID") String examPlanInfoID,@Param("appID") String appID);

    @Delete("DELETE FROM ExamineeArrange WHERE AppID = #{appID} AND ExamPlanInfoID = #{examPlanInfoID}")
    void delExamineeArrangesWhere(@Param("examPlanInfoID") String examPlanInfoID,@Param("appID") String appID);

    @Select("SELECT COUNT(*) FROM ExamineeArrange AS x JOIN ( SELECT * FROM ExamSubject WHERE ExamPlanInfoID = #{planId} ) AS y on x.ExamSubjectID = y.ExamSubjectID WHERE x.ExamPlanInfoID = y.ExamPlanInfoID AND x.AppID = y.AppID AND x.ExamPlanInfoID = #{planId} AND x.AppID = #{appId}")
    Integer getCountByPlanIdAndAppId(@Param(value = "planId") String planId,@Param(value = "appId") String appId);

    @SelectProvider(type = ExamineeArrangeProvider.class,method = "findAccidentList")
    List<AccidentInfo> findAccidentList(@Param("requestBody")Map<String, Object> requestBody,@Param(value = "appId") String appId);

    @Select("SELECT * FROM ExamineeArrange WHERE ExamPlanInfoID = #{planId} AND  PlanBeginTime > #{beginTime} AND PlanEndTime < #{endTime}")
    List<ExamineeArrange> findEAByBeginAndEndTime(@Param("planId")String planId,@Param("beginTime") String beginTime,@Param("endTime") String endTime);

    @Select("SELECT * FROM ExamineeArrange WHERE AppID = #{appId} AND ExamPlanInfoID = #{planId} AND ExamSceneID = #{sceneId} AND ExamineeID = #{examineeId} LIMIT 1")
    ExamineeArrange findExamineeArrangeFirstOrDefault(@Param("appId")String appId,@Param("planId") String planId,@Param("sceneId") String sceneId,@Param("examineeId") String examineeId);

    @Select("SELECT * FROM ExamineeArrange WHERE ExamPlanInfoID = #{examPlanInfoID} AND ExamSubjectID = #{examSubjectID} AND AppID = #{appID} AND ExamineeName = #{examineeName} LIMIT 1")
    ExamineeArrange getFirstByWhere(@Param(value = "examPlanInfoID") String examPlanInfoID,@Param(value = "examSubjectID") String examSubjectID,@Param(value = "appID") String appID,@Param(value = "examineeName") String examineeName);

    @Select("SELECT * FROM ExamineeArrange WHERE ExamPlanInfoID = #{examPlanInfoId} AND ExamSubjectID = #{examSubjectId} AND ExamineeID = #{examineeId} LIMIT 1")
    ExamineeArrange findExamBeginTime(@Param("examineeId") String examineeId,@Param("examPlanInfoId") String examPlanInfoId,@Param("appId") String appId,@Param("examSubjectId") String examSubjectId);

    @Select("SELECT\n" +
            "\t1\n" +
            "FROM\n" +
            "\texamineearrange arrange\n" +
            "JOIN examplaninfo examplan ON arrange.ExamPlanInfoID = examplan.ExamPlanInfoID \n" +
            "JOIN examscenes scenes ON scenes.ExamPlanInfoID = examplan.ExamPlanInfoID\n" +
            "WHERE\n" +
            "\tarrange.ExamPlanInfoID = #{examPlanID}\n" +
            "AND arrange.ExamSubjectID = #{examSubjectID}\n" +
            "AND scenes.PlanEndTime > NOW() LIMIT 1")
    Integer findArrangeInfoBYPlanIDANDSubjectID(@Param(value = "examPlanID")String examPlanID,@Param(value = "examSubjectID") String examSubjectID);
}
